package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.junit.Test;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class TestFindEmployee {
    public static QueryRunner qr;

    static {
        //使用DBUtils工具加载
        qr = new QueryRunner(DruidUtils.getDataSource());
    }

    /**
     * 查询所有的员工信息 (不包含没有部门的员工)。
     */
    @Test
    public void testFindAll() throws SQLException {

        //需求1:  查询所有的员工信息 (不包含没有部门的员工)
        String sql = "select * from employee e left join dept d on e.did = d.id where did is not null";

        List<Object[]> query = qr.query(sql, new ArrayListHandler());

        for (Object[] objects : query) {
            System.out.println(Arrays.toString(objects));
        }

    }

    /**
     * 查询每个员工的 姓名, 薪资 和 所属部门名称
     * @throws SQLException
     */
    @Test
    public void testFindNameSalaryAndDepartment() throws SQLException {

        //需求1:  查询所有的员工信息 (不包含没有部门的员工)
        String sql = "select e.name, e.salary, d.deptname from employee e left join dept d on e.did = d.id ";

        List<Object[]> query = qr.query(sql, new ArrayListHandler());

        for (Object[] objects : query) {
            System.out.println(Arrays.toString(objects));
        }
    }

}
